/*eslint @typescript-eslint/camelcase: "off"*/

import XLSX from "xlsx";

function countStar(starStrItem?: { v: string }) {
  if (!starStrItem) return 0;
  const starStr = starStrItem.v;
  if (!starStr) return 0;
  const starArr = starStr.split("");
  const starCount = starArr.filter(item => item === "★").length;
  const halfStarCount = starArr.filter(item => item === "☆").length;
  return starCount + halfStarCount * 0.5;
}

function getSheetNumber(sheetItem?: { v: number }) {
  if (!sheetItem) return null;
  if (isNaN(sheetItem.v)) return null;

  return Math.floor(sheetItem.v * 100) / 100;
}

interface ItemData {
  name: string;
  code: string;
  creatline: number | null;
  addline: number | null;
  heavyline: number | null;
  research_degree: number;
  safe_degree: number;
  rating: number;
  comment: string;
  qualification: string;
}

export function getXlsData(xls: File) {
  return new Promise<ItemData[]>(resolve => {
    const reader = new FileReader();
    reader.readAsBinaryString(xls);
    reader.onloadend = function() {
      const xl = XLSX.read(this.result, { type: "binary" });
      const fullSheet = xl.Sheets["全表"];
      const keys = Object.keys(fullSheet)
        .filter(key => key.indexOf("AA") === 0 && key !== "AA3")
        .map(val => val.replace("AA", ""));
      const formatData: ItemData[] = [];
      keys.forEach(itemKey => {
        formatData.push({
          name: fullSheet[`C${itemKey}`].v,
          code: fullSheet[`B${itemKey}`].v.toLowerCase(),
          creatline: getSheetNumber(fullSheet[`AB${itemKey}`]),
          addline: getSheetNumber(fullSheet[`AC${itemKey}`]),
          heavyline: getSheetNumber(fullSheet[`AD${itemKey}`]),
          research_degree: countStar(fullSheet[`AW${itemKey}`]),
          safe_degree: countStar(fullSheet[`AX${itemKey}`]),
          rating: countStar(fullSheet[`AY${itemKey}`]),
          comment: fullSheet[`AZ${itemKey}`] && fullSheet[`AZ${itemKey}`].v,
          qualification:
            fullSheet[`BA${itemKey}`] && fullSheet[`BA${itemKey}`].v
        });
      });
      resolve(formatData);
    };
  });
}
